import facade
from xjlibrary.our_file_dir import BaseDir

curPath = BaseDir.get_file_dir_absolute(__file__)


class test(object):

    def __init__(self):
        self.new = set()
        self.old = set()
        self.configfile = BaseDir.get_new_path(curPath, "db.ini")
        self.logger = facade.get_streamlogger()
        self.mysqlutils = facade.MysqlUtiles(self.configfile, "db", logger=self.logger)

    def get_new(self):
        with open("./new.txt", "r", encoding="utf-8") as f:
            for fLine in f:
                fLine = fLine.strip()
                fLine = fLine.replace("\"", "").strip()
                self.new.add(fLine)

    def get_old(self):
        with open("./old.txt", "r", encoding="utf-8") as f:
            for fLine in f:
                fLine = fLine.strip()
                fLine = fLine.split("	")[0]
                self.old.add(fLine)

    def get_file(self):
        with open("F:\excel\AN_20190925.txt", "r", encoding="utf-8") as f:
            for fLine in f:
                fLine = fLine.strip()
                if fLine:
                    self.new.add(fLine)

    def exc_sql(self):
        sql = "update articlenew set stat=5 where AccessionNumber in {}".format(tuple(self.new))
        self.mysqlutils.ExeSqlToDB(sql)

    def cha(self):
        print(len(self.new))
        print(len(self.old))
        self.op = self.new - self.old
        print(len(self.op))
        with open("./cha.txt", 'w', encoding="utf-8") as f:
            for line in self.op:
                f.write(line + "\n")

    def sql(self):
        List1 = []
        with open("./cha.txt", "r", encoding="utf-8") as f:
            for fLine in f:
                fLine = fLine.strip()
                List1.append(fLine)
                if len(List1) > 100000:
                    sql = "update `articlenew` set ref_stat=0 where AccessionNumber in {}".format(tuple(List1))
                    self.mysqlutils.ExeSqlToDB(sql)
                    List1.clear()
        if len(List1) > 0:
            sql = "update `articlenew` set ref_stat=0 where AccessionNumber in {}".format(tuple(List1))
            self.mysqlutils.ExeSqlToDB(sql)
            List1.clear()


if __name__ == "__main__":
    t = test()
    # t.get_new()
    # t.get_old()
    # t.sql()
    t.get_file()
    t.exc_sql()

# import facade
# from xjlibrary.our_file_dir import BaseDir

#
# class readDB3(object):
#     def __init__(self):
#         self.logger = facade.get_streamlogger()
#         self.new_db3_path = r'C:\Users\xuzhu\Desktop\ei_zt_20190827.db3'
#         self.db3_zt = facade.Sqlite3Utiles(logger=self.logger).Sqlite3DBConnectFromFilePath(
#             self.new_db3_path, encoding="utf-8")
#         self.count = 0
#         self.sets = set()
#
#     def select(self):
#         sql = "select lngid,title from modify_title_info_zt where rawid IS NULL limit 10000"
#         rows = self.db3_zt.SelectFromSqlliteFetchall(sql)
#         self.count = self.count + len(rows)
#         print(self.count)
#         if rows:
#             strings = ""
#             for row in rows:
#                 self.sets.add(row[0])
#                 oldlngid = row[0]
#                 newlngid = row[1]
#                 strings = strings + oldlngid + "★" + newlngid + "\n"
#             BaseDir.single_add_file("./ei_NewToOldLngid_1.txt", strings)
#
#             sql = "update modify_title_info_zt set rawid='1' where lngid in {}".format(tuple(self.sets))
#             self.db3_zt.ExeSqlliteSql(sql)
#             self.sets.clear()
#
#         else:
#             return "break"
#
#
# if __name__ == "__main__":
#     db3 = readDB3()
#     while True:
#         results = db3.select()
#         if results == "break":
#             break

#
# import json
# import os
# import time
# import xlrd
# from collections import OrderedDict
# import codecs
#
# sPath = r"F:\excel"
#
# dicts = {'USA': 'US',
#          'CHILE': 'CL',
#          'BOSNIA & HERZEGOVINA': 'BA',
#          'CANADA': 'CA',
#          'ESTONIA': 'EE',
#          'BRAZIL': 'BR',
#          'ALLSA': 'ZA',
#          'SOUTH KOREA': 'KR',
#          'CHINA MAINLAND': 'CN',
#          'SAUDI ARABIA': 'SA',
#          'EGYPT': 'EG',
#          'SINGAPORE': 'SG',
#          'FRANCE': 'FR',
#          'AUSTRALIA': 'AU',
#          'SWITZERLAND': 'CH',
#          'COLOMBIA': 'CO',
#          'DENMARK': 'DK',
#          'ENGLAND': 'GB',
#          'ITALY': 'IT',
#          'NETHERLANDS': 'NL',
#          'PORTUGAL': 'PT',
#          'GERMANY (FED REP GER)': 'DE',
#          'CZECH REPUBLIC': 'CZ',
#          'SPAIN': 'ES',
#          'VENEZUELA': 'VE',
#          'JAPAN': 'JP',
#          'POLAND': 'PL',
#          'NEW ZEALAND': 'NZ'}
#
#
# def getColumnIndex(table, columnName):
#     columnIndex = None
#     for i in range(table.ncols):
#         if table.cell_value(0, i) == columnName:
#             columnIndex = i
#             break
#     return columnIndex
#
#
# def exceltojson(filePath):
#     # 如果出现xlsx错误 就直接跳过
#     try:
#         wb = xlrd.open_workbook(filePath)
#     except:
#         return
#
#     sh = wb.sheet_by_index(0)
#
#     title = sh.row_values(0)
#     # Listset = set()
#     for rownum in range(1, sh.nrows):
#         rowvalue = sh.row_values(rownum)
#         single = OrderedDict()
#         for colnum in range(0, len(rowvalue)):
#             single[title[colnum]] = rowvalue[colnum]
#         country = single["country"]
#         if country:
#             if len(country) != 2:
#                 print(country)
#                 single["country"] = dicts[country]
#
#         j = json.dumps(single, ensure_ascii=False)
#
#         with codecs.open(filename, "a", "utf-8") as f:
#             f.write(j + "\r\n")
#
#
# def main():
#     global filename
#     filename = str(int(time.time())) + ".json"
#     con = 0
#     for path, dirNames, fileNames in os.walk(sPath):
#         for fileName in fileNames:
#             filePath = os.path.join(path, fileName)
#             print(filePath)
#             exceltojson(filePath)
#             con += 1
#             print("完成文件个数{}".format(con))
#
#
# if __name__ == "__main__":
#     main()
